﻿USE [racks]
GO
/****** Object:  Table [dbo].[dcs]    Script Date: 12/01/2011 03:13:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dcs](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](256) NULL,
	[address] [varchar](256) NOT NULL,
	[power] [int] NULL,
	[notes] [varchar](4096) NULL,
	[width] [int] NOT NULL,
	[height] [int] NOT NULL,
	[entrance_left] [int] NOT NULL,
	[entrance_top] [int] NOT NULL,
 CONSTRAINT [PK_dcs] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[vendors]    Script Date: 12/01/2011 03:13:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[vendors](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](256) NOT NULL,
	[color] [varchar](32) NULL,
PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  View [dbo].[v_dcs]    Script Date: 12/01/2011 03:13:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[v_dcs] as select 
id, name, address, power, notes
from dcs
GO
/****** Object:  Table [dbo].[racks]    Script Date: 12/01/2011 03:13:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[racks](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](256) NULL,
	[units] [int] NULL,
	[notes] [varchar](4096) NULL,
	[dc_id] [int] NULL,
	[vendor_id] [int] NULL,
	[top] [int] NOT NULL,
	[left] [int] NOT NULL,
	[responsible] [varchar](512) NULL,
 CONSTRAINT [PK_racks] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[inventory]    Script Date: 12/01/2011 03:13:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[inventory](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](256) NULL,
	[model] [varchar](256) NULL,
	[type] [varchar](256) NULL,
	[sn] [varchar](256) NULL,
	[in] [varchar](256) NULL,
	[pusage] [int] NULL,
	[pstate] [bit] NULL,
	[units] [varchar](256) NULL,
	[mgmtip] [varchar](15) NULL,
	[notes] [varchar](4096) NULL,
	[color] [varchar](256) NULL,
	[rack_id] [int] NULL,
	[vendor_id] [int] NULL,
	[responsible] [varchar](512) NULL,
 CONSTRAINT [PK_inventory] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  View [dbo].[v_racks]    Script Date: 12/01/2011 03:13:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[v_racks] as
select r.id, r.name, r.units, r.notes, d.name as dc, v.name as vendor
from dcs d
inner join racks r on d.id = r.dc_id
left join vendors v on v.id = r.vendor_id
;
GO
/****** Object:  Table [dbo].[reserved_units]    Script Date: 12/01/2011 03:13:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[reserved_units](
	[rack_id] [int] NOT NULL,
	[unit] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[rack_id] ASC,
	[unit] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[server_ips]    Script Date: 12/01/2011 03:13:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[server_ips](
	[server_id] [int] NOT NULL,
	[ip] [varchar](20) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[server_id] ASC,
	[ip] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  View [dbo].[v_inventory]    Script Date: 12/01/2011 03:13:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view  [dbo].[v_inventory] as
select d.id as dc_id, d.name as dc, r.name as rack, i.id, i.name, i.model, i.type, i.sn, i.[in], i.pusage, i.units, i.mgmtip, i.notes, i.color,
v.name as vendor, v.color as vcolor, srv_ips.ips as srvip 
from inventory i 
left join racks r on r.id = i.rack_id
left join dcs d on d.id = r.dc_id
left join vendors v on i.vendor_id = v.id
left join
(select distinct server_id,
(stuff((select ',' + ip from server_ips i2 where i1.server_id = i2.server_id order by ip
for xml path(''), type, root).value('root[1]','nvarchar(max)'),1,1,'')) as ips
from server_ips i1) as srv_ips
on srv_ips.server_id = i.id
GO
/****** Object:  Default [DF__dcs__width__145C0A3F]    Script Date: 12/01/2011 03:13:49 ******/
ALTER TABLE [dbo].[dcs] ADD  DEFAULT ((1500)) FOR [width]
GO
/****** Object:  Default [DF__dcs__height__15502E78]    Script Date: 12/01/2011 03:13:49 ******/
ALTER TABLE [dbo].[dcs] ADD  DEFAULT ((900)) FOR [height]
GO
/****** Object:  Default [DF__dcs__entrance_le__182C9B23]    Script Date: 12/01/2011 03:13:49 ******/
ALTER TABLE [dbo].[dcs] ADD  DEFAULT ((0)) FOR [entrance_left]
GO
/****** Object:  Default [DF__dcs__entrance_to__1920BF5C]    Script Date: 12/01/2011 03:13:49 ******/
ALTER TABLE [dbo].[dcs] ADD  DEFAULT ((880)) FOR [entrance_top]
GO
/****** Object:  Default [DF__racks__top__164452B1]    Script Date: 12/01/2011 03:13:49 ******/
ALTER TABLE [dbo].[racks] ADD  DEFAULT ((0)) FOR [top]
GO
/****** Object:  Default [DF__racks__left__173876EA]    Script Date: 12/01/2011 03:13:49 ******/
ALTER TABLE [dbo].[racks] ADD  DEFAULT ((0)) FOR [left]
GO
/****** Object:  ForeignKey [FK__inventory__rack___0EA330E9]    Script Date: 12/01/2011 03:13:49 ******/
ALTER TABLE [dbo].[inventory]  WITH CHECK ADD FOREIGN KEY([rack_id])
REFERENCES [dbo].[racks] ([id])
ON DELETE CASCADE
GO
/****** Object:  ForeignKey [FK__inventory__vendo__0AD2A005]    Script Date: 12/01/2011 03:13:49 ******/
ALTER TABLE [dbo].[inventory]  WITH CHECK ADD FOREIGN KEY([vendor_id])
REFERENCES [dbo].[vendors] ([id])
ON DELETE SET NULL
GO
/****** Object:  ForeignKey [FK__racks__dc_id__0DAF0CB0]    Script Date: 12/01/2011 03:13:49 ******/
ALTER TABLE [dbo].[racks]  WITH CHECK ADD FOREIGN KEY([dc_id])
REFERENCES [dbo].[dcs] ([id])
ON DELETE CASCADE
GO
/****** Object:  ForeignKey [FK__racks__vendor_id__09DE7BCC]    Script Date: 12/01/2011 03:13:49 ******/
ALTER TABLE [dbo].[racks]  WITH CHECK ADD FOREIGN KEY([vendor_id])
REFERENCES [dbo].[vendors] ([id])
ON DELETE SET NULL
GO
/****** Object:  ForeignKey [FK__reserved___rack___20C1E124]    Script Date: 12/01/2011 03:13:49 ******/
ALTER TABLE [dbo].[reserved_units]  WITH CHECK ADD FOREIGN KEY([rack_id])
REFERENCES [dbo].[racks] ([id])
ON DELETE CASCADE
GO
/****** Object:  ForeignKey [FK__server_ip__serve__1367E606]    Script Date: 12/01/2011 03:13:49 ******/
ALTER TABLE [dbo].[server_ips]  WITH CHECK ADD FOREIGN KEY([server_id])
REFERENCES [dbo].[inventory] ([id])
ON DELETE CASCADE
GO
